---
title: "Core Tables"
type: concept
created: 2026-04-18
updated: 2026-04-18
sources: ["raw/articles/01-data-model.md"]
tags: [data-model, ddl, schema, mysql]
---

# Core Tables

Full DDL for the most critical tables across the Pickatale platform. All tables exist in the shared MySQL 8 instance (`shared-db`, port 3316). Source: code audit 2026-04-18 (status noted per table).

## Account Center DB (`account_center`)

### `users` ✅ Exists

```sql
CREATE TABLE users (
  id               INT PRIMARY KEY AUTO_INCREMENT,
  uuid             VARCHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),
  name             VARCHAR(255) NOT NULL,
  email            VARCHAR(255) NOT NULL UNIQUE,
  password_hash    VARCHAR(255) NOT NULL,
  role             ENUM('teacher','parent','school_admin','platform_admin') NOT NULL,
  state            ENUM('pending_verification','active','suspended','archived')
                   NOT NULL DEFAULT 'pending_verification',
  email_verified   BOOLEAN NOT NULL DEFAULT FALSE,
  school_id        INT NULL,
  failed_attempts  INT NOT NULL DEFAULT 0,
  locked_until     DATETIME NULL,
  created_at       DATETIME NOT NULL DEFAULT NOW(),
  updated_at       DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  INDEX(email), INDEX(school_id), INDEX(state)
);
```

### `schools` ✅ Exists

```sql
CREATE TABLE schools (
  id               INT PRIMARY KEY AUTO_INCREMENT,
  name             VARCHAR(255) NOT NULL,
  country          VARCHAR(100) NOT NULL DEFAULT '',
  admin_user_id    INT NULL,
  state            ENUM('pending','active','suspended','archived') NOT NULL DEFAULT 'pending',
  auto_approve_parent_claims BOOLEAN NOT NULL DEFAULT FALSE,
  created_at       DATETIME NOT NULL DEFAULT NOW(),
  updated_at       DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW()
);
```

### `sessions` ✅ Exists

```sql
CREATE TABLE sessions (
  id               INT PRIMARY KEY AUTO_INCREMENT,
  user_id          INT NULL,       -- NULL for child sessions (use student_id)
  student_id       INT NULL,       -- child sessions only
  role             ENUM('teacher','parent','school_admin','platform_admin','child') NOT NULL,
  token_hash       VARCHAR(64) NOT NULL UNIQUE,  -- sha256(session_uuid)
  expires_at       DATETIME NOT NULL,
  invalidated_at   DATETIME NULL,
  ip               VARCHAR(45),
  user_agent       VARCHAR(500),
  created_at       DATETIME NOT NULL DEFAULT NOW(),
  INDEX(token_hash), INDEX(user_id), INDEX(student_id)
);
```

## Teacher Portal DB (`teacher_portal`)

### `students` ✅ Exists

```sql
CREATE TABLE students (
  id                        INT PRIMARY KEY AUTO_INCREMENT,
  uuid                      VARCHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),  -- learner_id
  name                      VARCHAR(255) NOT NULL,
  username                  VARCHAR(50) NOT NULL UNIQUE,
  pin_hash                  VARCHAR(255) NOT NULL,  -- bcrypt cost 10
  -- NOTE: no class_id here — a student can belong to multiple classes
  -- Use class_memberships join table for class assignment
  school_id                 INT NULL,
  year_level                INT NULL,
  language                  VARCHAR(10) NOT NULL DEFAULT 'en',
  state                     ENUM('created','active','inactive','archived') NOT NULL DEFAULT 'created',
  placement_test_completed  BOOLEAN NOT NULL DEFAULT FALSE,
  failed_attempts           INT NOT NULL DEFAULT 0,
  locked                    BOOLEAN NOT NULL DEFAULT FALSE,
  parent_count              INT NOT NULL DEFAULT 0,
  entitlement_tier          ENUM('free','full') NOT NULL DEFAULT 'free',
  miles                     DECIMAL(10,2) NOT NULL DEFAULT 0,
  tokens                    INT NOT NULL DEFAULT 0,
  created_at                DATETIME NOT NULL DEFAULT NOW(),
  updated_at                DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  INDEX(school_id), INDEX(uuid)
);

CREATE TABLE class_memberships (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  student_id   INT NOT NULL,
  class_id     INT NOT NULL,
  enrolled_at  DATETIME NOT NULL DEFAULT NOW(),
  archived_at  DATETIME NULL,           -- NULL = active; set to archive without deleting
  UNIQUE KEY uq_student_class (student_id, class_id),
  INDEX(class_id),
  INDEX(student_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (class_id)   REFERENCES classes(id)
);
-- Seat count query (per class): SELECT COUNT(*) FROM class_memberships
--   WHERE class_id = ? AND archived_at IS NULL
-- Cap enforced at class level, not license level.
-- Same student in math class + history class = 1 seat in each (billed to each teacher separately)
```

### `classes` ✅ Exists

```sql
CREATE TABLE classes (
  id                     INT PRIMARY KEY AUTO_INCREMENT,
  class_name             VARCHAR(255) NOT NULL,
  year_level             INT NOT NULL,
  teacher_id             INT NOT NULL,
  school_id              INT NULL,
  curriculum_territory   VARCHAR(100) NULL,
  state                  ENUM('active','archived') NOT NULL DEFAULT 'active',
  archived_at            DATETIME NULL,
  created_at             DATETIME NOT NULL DEFAULT NOW(),
  updated_at             DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  INDEX(teacher_id), INDEX(school_id)
);
```

### `memberships` ✅ Exists

```sql
CREATE TABLE memberships (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  school_id    INT NOT NULL,
  user_id      INT NOT NULL,  -- Account Center users.id
  role         ENUM('teacher','school_admin') NOT NULL,
  state        ENUM('active','inactive') NOT NULL DEFAULT 'active',
  joined_at    DATETIME NOT NULL DEFAULT NOW(),
  UNIQUE(school_id, user_id)
);
```

## Reader App DB (`reader_app`)

### `reading_sessions` ✅ Exists

```sql
CREATE TABLE reading_sessions (
  id              INT PRIMARY KEY AUTO_INCREMENT,
  session_id      VARCHAR(36) NOT NULL UNIQUE,  -- UUID, client-generated
  learner_id      VARCHAR(36) NOT NULL,         -- students.uuid
  book_id         VARCHAR(100) NOT NULL,
  state           ENUM('open','completed','abandoned') NOT NULL DEFAULT 'open',
  pages_read      INT NOT NULL DEFAULT 0,
  total_pages     INT NULL,
  words_read      INT NOT NULL DEFAULT 0,
  started_at      DATETIME NOT NULL DEFAULT NOW(),
  ended_at        DATETIME NULL,
  last_event_at   DATETIME NULL,
  fk_level        DECIMAL(4,2) NULL,  -- level at time of reading
  created_at      DATETIME NOT NULL DEFAULT NOW(),
  INDEX(learner_id), INDEX(book_id), INDEX(state)
);
```

## Telemetry DB (`telemetry`)

### `events` ✅ Exists

```sql
CREATE TABLE events (
  id              BIGINT PRIMARY KEY AUTO_INCREMENT,
  event_id        VARCHAR(36) NOT NULL UNIQUE,  -- UUID, client-generated (dedup key)
  learner_id      VARCHAR(36) NOT NULL,
  session_id      VARCHAR(36) NOT NULL,
  event_type      ENUM('book_opened','page_turned','word_tapped','session_ended',
                       'quiz_completed','placement_completed','speedread_started',
                       'speedread_ended','book_abandoned') NOT NULL,
  book_id         VARCHAR(100) NULL,
  page_number     INT NULL,
  word            VARCHAR(255) NULL,
  time_on_page_ms INT NULL,
  payload         JSON NULL,
  client_ts       BIGINT NOT NULL,  -- Unix milliseconds
  created_at      DATETIME NOT NULL DEFAULT NOW(),
  INDEX(learner_id), INDEX(session_id), INDEX(event_type), INDEX(created_at)
  -- Retention: rows with created_at < now()-90d deleted by cron at 03:00 UTC
);
```

## Learner Bot DB (`learner_bot`)

### `learner_memories` ✅ Exists

```sql
CREATE TABLE learner_memories (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  learner_id   VARCHAR(36) NOT NULL,
  memory_type  VARCHAR(100) NOT NULL,
  value        JSON NOT NULL,
  confidence   DECIMAL(3,2) NOT NULL DEFAULT 1.00,
  created_at   DATETIME NOT NULL DEFAULT NOW(),
  updated_at   DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  INDEX(learner_id, memory_type)
);
-- memory_type values: assessment_result, curriculum_state, reading_pattern,
--                     engagement_signal, vocabularyGaps
```

### `vocabulary_gaps` ✅ Exists

```sql
CREATE TABLE vocabulary_gaps (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  learner_id   VARCHAR(36) NOT NULL,
  word         VARCHAR(255) NOT NULL,
  tap_count    INT NOT NULL DEFAULT 1,
  last_tapped  DATETIME NOT NULL DEFAULT NOW(),
  UNIQUE(learner_id, word)
);
```

### Billing — `teacher_licenses` (MUST BUILD)

> **⚠️ Confirmed by Sig Dug, 2026-04-18:** v1 billing unit is the **teacher license** — 1 license per teacher, covers all their classes. Each class is independently capped at 33 active students via `class_memberships` count. Do not build per-class billing tables.

```sql
-- v1 billing: teacher-license model (1 license per teacher, covers all classes)
CREATE TABLE teacher_licenses (
  id               VARCHAR(36)  NOT NULL PRIMARY KEY DEFAULT (UUID()),
  teacher_id       VARCHAR(36)  NOT NULL UNIQUE,   -- 1 license per teacher
  tier             ENUM('free','trial','teacher_paid','enterprise','gifted') NOT NULL DEFAULT 'free',
  billing_cycle    ENUM('monthly','yearly') NULL,
  state            ENUM('trialing','active','past_due','expired','cancelled') NOT NULL DEFAULT 'trialing',
  stripe_sub_id    VARCHAR(255) NULL,
  stripe_coupon_id VARCHAR(100) NULL,
  period_start     DATETIME     NULL,
  period_end       DATETIME     NULL,
  grace_ends_at    DATETIME     NULL,
  created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
);
```

**v1 tiers:** `free`, `trial`, `teacher_paid`, `enterprise`, `gifted`.
**Removed:** `parent_paid`, `school_paid`, `class_paid`, `class_licenses` — do not build, do not reference.
**Seat cap:** Per class, 33 max. Enforced by counting `class_memberships WHERE class_id = ? AND archived_at IS NULL`. Server returns 422 `CLASS_FULL` if count ≥ 33.

## Related Pages

- [[concepts/data-model/index|Data Model]] — service ownership map
- [[concepts/data-model/Entitlement Model]] — checkEntitlement() logic